import json

from flask import jsonify

from tzq.database_compare_system import dcs_bp
from tzq.database_compare_system.models.dcs_task_models import DatabaseInfo
from tzq.database_compare_system.services.table_info_sql import get_table_info_sql
from tzq.extensions import db
from tzq.util.ConnectJavaGatewayUtil import ConnectJavaGatewayUtil
from tzq.util.ExecuteBatchSQLScripts import ExecuteBatchSQLScripts

"""
服务类
"""
from contextlib import contextmanager


@contextmanager
def timeout_context(seconds=3):
    try:
        yield
    except TimeoutError as e:
        # 处理超时逻辑
        return jsonify({"code": 0, "msg": str(e), "error": str(e)}), 500


@dcs_bp.route('/api/check_connection/<int:id>', methods=['GET'])
def check_connection(id):
    """ 检查数据库链接是否能连通 """
    database_info = DatabaseInfo.query.get_or_404(id)
    v_sql = ""

    # print("database_info = ", str(database_info.to_dict()))
    # connect_name = database_info.connect_name
    # database_type = database_info.database_type
    host = database_info.host
    port = str(database_info.port)
    dbname = database_info.dbname
    username = database_info.username
    password = database_info.password
    # v_sql = ("select * from dblink('host=" + host +
    #          " dbname=" + dbname + " port=" + port + " user=" + username + " password=" + password +
    #          "', 'select 1 as xh') as t (xh text)")
    # print("v_sql = ", str(v_sql.strip()))
    try:
        connectJavaGatewayUtil = ConnectJavaGatewayUtil()
        url = "jdbc:opengauss://" + host + ":" + port + "/" + dbname
        v_result_json_string = connectJavaGatewayUtil.check_connect_opengauss_database(
            url,
            username,
            password
        )
        v_result = json.loads(v_result_json_string)
        # print(f"数据库的连通情况：{v_result}")
        print(f"数据库的连通情况v_result['code']：{v_result['code']}")
        if v_result['code'] == 1:
            return jsonify({"code": 1, "msg": "【OK】"})
        elif v_result['code'] == 0:
            return jsonify({"code": -1, "msg": "【ERROR】" + v_result['msg']})
    except Exception as e:
        return jsonify({"code": 0, "msg": str(e), "error": str(e)}), 500

    # try:
    #     # result = db.session.execute("SELECT pg_sleep(5)")  # 模拟超时查询
    #     # return jsonify(result.fetchall())
    #
    #     executor = ExecuteBatchSQLScripts(db)
    #     # v_result = executor.execute_sql_json(v_sql)
    #     # 使用方式
    #     with timeout_context(3):
    #         v_result = executor.execute_sql_json(v_sql)
    #         # result = db.session.execute("SELECT ...")
    #
    #     # return jsonify({"code": 1, "msg": "【OK】执行SQL成功!"})
    #     v_result = v_result.get_json()
    #
    #     if v_result['code'] == 1:
    #         return jsonify({"code": 1, "msg": "【OK】"})
    #     elif v_result['code'] == 0:
    #         return jsonify({"code": -1, "msg": "【ERROR】" + v_result['msg']})
    #
    # except Exception as e:
    #     if "out" in str(e):
    #         return jsonify({"code": 0, "msg": str(e), "error": "查询超时"}), 504


@dcs_bp.route('/api/initDatabaseData/<int:id>', methods=['GET'])
def init_database_data(id):
    """
        初始化数据库链接的相关信息
        1、根据数据源的信息，把表信息通过数据链dblink查出来的数据插入到 dc_table_info_t 表
    """
    database_info = DatabaseInfo.query.get_or_404(id)
    try:
        connect_name = database_info.connect_name
        database_type = database_info.database_type
        host = database_info.host
        port = str(database_info.port)
        dbname = database_info.dbname
        username = database_info.username
        password = database_info.password
        # v_sql = \
        #     ("select * from dblink('host=" + host +
        #      " dbname=" + dbname + " port=" + port + " user=" + username + " password=" + password +
        #      "', 'select 1 as xh') as t (xh text)")
        v_table_info_sql = get_table_info_sql(database_type, connect_name)
        v_sql = """
        delete from dc_table_info_t where source = '""" + connect_name + """';
INSERT INTO dc_table_info_t (source, database_type, schema, owner, table_name, table_comment, column_num, column_name, data_type, data_default, nullable, pkey, column_comment)
select source, database_type, schema, owner, table_name, table_comment, column_num, column_name, data_type, data_default, nullable, pkey, column_comment
  from dblink('host=""" + host + """ dbname=""" + dbname + """ port=""" + port + """ user=""" + username + """ password=""" + password + """',
'
""" + v_table_info_sql + """
')
as t (
  source text,
  database_type text,
  schema text,
  owner text,
  table_name text,
  table_comment text,
  column_num int8,
  column_name text,
  data_type text,
  data_default text,
  nullable text,
  pkey text,
  column_comment text
);"""
        executor = ExecuteBatchSQLScripts(db)
        v_result = executor.execute_sql_json(v_sql)
        v_result = v_result.get_json()
        if v_result['code'] == 1:
            return jsonify({"code": 1, "msg": "【OK】"})
        elif v_result['code'] == 0:
            return jsonify({"code": -1, "msg": "【ERROR】" + v_result['msg']})
    except Exception as e:
        return jsonify({"code": 0, "msg": str(e), "error": str(e)}), 500
